package com.dshore.messagecenter.dao.topicqueue;

import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;

import com.dshore.messagecenter.model.basedata.MdApp;
import com.dshore.messagecenter.model.topicqueue.MdTopic;
import com.dshore.messagecenter.model.topicqueue.MdTopicAccess;
import com.dshore.messagecenter.model.topicqueue.MdTopicSend;
import com.dshore.messagecenter.util.UUIDUtil;



@Repository
public class TopicQueueDao {

	@Autowired
	JdbcTemplate jdbcTemplate;
	
	/**
	 * 根据topicQueueNo查询主题队列表的数据
	 */
	public MdTopic queryQueueInfo(String topicQueueNo) {
		String sql = " select * from T_MD_TOPIC where TOPIC_QUEUE_NO = ? and DEL_FLAG = 0";
		RowMapper<MdTopic> rowMapper = new BeanPropertyRowMapper<>(MdTopic.class);
		try {
			return jdbcTemplate.queryForObject(sql, rowMapper, topicQueueNo);
		} catch (DataAccessException e) {
			e.printStackTrace();
			return null;
		}
	}
	
	/**
	 * 根据topicQueueNo查询接入信息和queuekey
	 */
	public List<MdTopicAccess> queryAccessByTopicSign(String topicQueueNo) {
		String sql = " select acc.*, app.QUEUE_KEY from T_MD_TOPIC_ACCESS acc "
				+ "LEFT JOIN T_MD_APP app "
				+ "ON acc.ACCESS_APPID = app.ID "
				+ "where acc.TOPIC_QUEUE_NO = ? and acc.DEL_FLAG = 0";
		RowMapper<MdTopicAccess> rowMapper = new BeanPropertyRowMapper<>(MdTopicAccess.class);
		return jdbcTemplate.query(sql,rowMapper,topicQueueNo);
	}
	
	/**
	 * 根据topicQueueNo查询发送信息和queuekey
	 */
	public List<MdTopicSend> querySendByTopicSign(String topicQueueNo) {
		String sql = " select send.*, send.TARGET_APPID as TARGET_APP_ID, app.QUEUE_KEY as queueKey from T_MD_TOPIC_SEND send "
				+ " LEFT JOIN T_MD_APP app "
				+ " ON send.TARGET_APPID = app.ID "
				+ " where send.TOPIC_QUEUE_NO = ? and send.DEL_FLAG = 0";
		RowMapper<MdTopicSend> rowMapper = new BeanPropertyRowMapper<>(MdTopicSend.class);
		return jdbcTemplate.query(sql,rowMapper,topicQueueNo);
	}
	
	
	/**
	 * 根据topicQueueNo更新队列表的vhost虚拟主机名称和exchange交换机名称
	 */
	public Integer updateQueueVE(String vhostName, String exchangeName,String status, String id) {
		String sql= "update T_MD_TOPIC set VHOST_NAME = ?,EXCHANGE_NAME = ?,STATUS = ? where ID =?";	
		return jdbcTemplate.update(sql, vhostName, exchangeName,status,id);
	}
	
	/**
	 * 根据topicQueueNo更新access表的queueName
	 */
	public Integer updateAccessQueueName(String queueName, String id) {
		String sql= "update T_MD_TOPIC_ACCESS set QUEUE_NAME =? where ID =?";	
		return jdbcTemplate.update(sql, queueName, id);
	}
	

	/**
	 * 根据topicQueueNo批量更新send表的queueName
	 */
	public Integer updateSendQueueName(List<Object[]> list) {
		String sql= "update T_MD_TOPIC_SEND set QUEUE_NAME =? where ID =?";	
		int[] i =  jdbcTemplate.batchUpdate(sql, list);
		return i.length;
	}
	
	
	
	

	/**
	 * 通过编码查询模板名称
	 * @param topicQueueNo
	 * @return
	 */
	public MdTopic queryMsgTemplate(String topicQueueNo) {
		String sql= "select T_MD_TEMPLATE.TEMPLATE_NAME templateName,T_MD_TOPIC.id from T_MD_TOPIC LEFT JOIN T_MD_TEMPLATE ON T_MD_TOPIC.TEMPLATE_ID=T_MD_TEMPLATE.ID where T_MD_TOPIC.TOPIC_QUEUE_NO =? and T_MD_TOPIC.DEL_FLAG = 0";	
		RowMapper<MdTopic> rowMapper = new BeanPropertyRowMapper<>(MdTopic.class);
		return jdbcTemplate.queryForObject(sql.toString(), rowMapper, topicQueueNo);
	}
	/**
	 * 根据id主键更新topicQueueName
	 */
	public int updateQueueName(String topicId, String topicQueueName) {
		String sql= "update T_MD_TOPIC set TOPIC_QUEUE_NAME =? where id =?";	
		return jdbcTemplate.update(sql, topicQueueName,topicId);
	}
	/**
	 * 根据id主键查询主题队列名称
	 */
	public String queryTQName(String id) {
		String sql = " SELECT TOPIC_QUEUE_NAME from T_MD_TOPIC where ID = ?";
		return jdbcTemplate.queryForObject(sql, String.class, id);
	}
	/**
	 * 根据id主键查询主题队列所有信息
	 */
	public MdTopic queryById(String id) {
		String sql = " SELECT * from T_MD_TOPIC where ID = ? ";
		RowMapper<MdTopic> rowMapper = new BeanPropertyRowMapper<>(MdTopic.class);
		return jdbcTemplate.queryForObject(sql, rowMapper, id);
	}
	/**
	 * 查询所有主题队列
	 * @return
	 */
	public List<MdTopic> queryAllQueueTopic() {
		String sql= "SELECT * from T_MD_TOPIC where DEL_FLAG = 0 ";	
		RowMapper<MdTopic> rowMapper = new BeanPropertyRowMapper<>(MdTopic.class);
		return jdbcTemplate.query(sql, rowMapper);
	}
	/**
	 * 查询队列名称和虚拟主机名称(删除时把队列删了)
	 * @param id
	 * @return
	 */
	public MdTopic queryQueueById(String id) {
		String sql = " SELECT EXCHANGE_NAME,VHOST_NAME,ACCESS_TYPE from T_MD_TOPIC where ID = ?";
		RowMapper<MdTopic> rowMapper = new BeanPropertyRowMapper<>(MdTopic.class);
		return jdbcTemplate.queryForObject(sql,rowMapper, id);
	}
	
	/** 获取所有接入方式 */
	public List<Map<String, Object>> getAccessType() {
		String sql = " SELECT DICT_CODE as CODE, DICT_NAME NAME from T_SYS_DICT "
				+ " where DICT_TYPE = 'accessType' ";
		return jdbcTemplate.queryForList(sql);
	}


	public long queryCountByPrama(MdTopic topic, String checkAppId) {
		StringBuffer sql = new StringBuffer().append("select count(1) from T_MD_TOPIC where DEL_FLAG=0");
		List<Object> list=new ArrayList<>();
		if(StringUtils.isNotBlank(checkAppId)) {
			sql.append(" and TOPIC_QUEUE_NO in " + 
					"	(select TOPIC_QUEUE_NO from T_MD_TOPIC_ACCESS " + 
					"	where ACCESS_APPID = ?)");
			list.add(checkAppId);
		}
		if(StringUtils.isNotBlank(topic.getAccessType())) {
			sql.append(" and ACCESS_TYPE = ?");
			list.add(topic.getAccessType());
		}
		if(StringUtils.isNotBlank( topic.getTopicQueueName() )) {
			sql.append("and TOPIC_QUEUE_NAME like ?");
			list.add("%" + topic.getTopicQueueName() + "%");
		}
		if(StringUtils.isNotBlank( topic.getTopicQueueNo() )) {
			sql.append("and TOPIC_QUEUE_NO like ?");
			list.add("%" + topic.getTopicQueueNo() + "%");
		}
		Object[]args=list.toArray();
		return jdbcTemplate.queryForObject(sql.toString(), Long.class,args);
	}

	public List<Map<String, Object>> queryListByPrama(MdTopic topic, String checkAppId) {
		StringBuffer sql = new StringBuffer();
		sql.append(" select * from (");
		sql.append(" select A.*, rownum rn from (\r\n" + 
				"		select topic.*,dictsta.DICT_NAME as statusVal,dictacc.DICT_NAME as accessTypeVal,temp.TEMPLATE_NAME, " + 
				"		app.APP_NAME,(select count(*) from T_MD_TOPIC_SEND send where send.TOPIC_QUEUE_NO = topic.TOPIC_QUEUE_NO " + 
				"		and send.DEL_FLAG = 0) as tAppNum " + 
				"		from T_MD_TOPIC topic " + 
				"		left join T_SYS_DICT dictsta on topic.STATUS = dictsta.DICT_CODE " + 
				"		left join T_SYS_DICT dictacc on topic.ACCESS_TYPE = dictacc.DICT_CODE " + 
				"		left join T_MD_TEMPLATE temp on topic.TEMPLATE_ID = temp.ID " + 
				"		left join T_MD_TOPIC_ACCESS acc on topic.TOPIC_QUEUE_NO = acc.TOPIC_QUEUE_NO " + 
				"		left join T_MD_APP app on acc.ACCESS_APPID = app.ID " + 
				"		where dictsta.DICT_TYPE = 'topicQueueStatus' and dictacc.DICT_TYPE = 'accessType' and topic.DEL_FLAG = 0 ");
		List<Object> list=new ArrayList<>();
		if(StringUtils.isNotBlank(checkAppId)) {
			sql.append("	and (app.APP_TYPE = ? or app.ID = ? )");
			list.add(checkAppId);
			list.add(checkAppId);
		}
		if(StringUtils.isNotBlank(topic.getAccessType())) {
			sql.append(" and topic.ACCESS_TYPE = ?");
			list.add(topic.getAccessType());
		}
		if(StringUtils.isNotBlank( topic.getTopicQueueName() )) {
			sql.append("and topic.TOPIC_QUEUE_NAME like ?");
			list.add("%" + topic.getTopicQueueName() + "%");
		}
		if(StringUtils.isNotBlank( topic.getTopicQueueNo() )) {
			sql.append("and topic.TOPIC_QUEUE_NO like ?");
			list.add("%" + topic.getTopicQueueNo() + "%");
		}
		sql.append(" order by topic.CREATED_TIME desc ");
		sql.append(" ) A   )");
		sql.append(" where rn between ? and ? ");
		list.add(topic.getOffset()+1);
		list.add(topic.getOffset()+topic.getLimit());
//		sql.append(" order by CREATED_TIME desc ");
		Object[]args=list.toArray();
//		System.out.println("查询信息sql: " + sql);
		List<Map<String, Object>> result = jdbcTemplate.queryForList(sql.toString(), args);
		return result;
	}


	public String queryStatus(String status) {
		StringBuffer sql = new StringBuffer().append(
				"select DICT_NAME from T_SYS_DICT where DICT_TYPE = 'topicQueueStatus' and DICT_CODE=?");
		return jdbcTemplate.queryForObject(sql.toString(), String.class, status);
	}


	public String queryAccessType(String accessType) {
		StringBuffer sql = new StringBuffer().append(
			"select DICT_NAME from T_SYS_DICT where DICT_TYPE = 'accessType' and DICT_CODE=?");
		return jdbcTemplate.queryForObject(sql.toString(), String.class, accessType);
	}


	/*public List<Map<String, Object>> queryOriginApp(String originAppId) {
		String sql = "SELECT t.ID, t.NAME, t.P_ID FROM T_MD_APP_TYPE t "
				+ " start with P_ID is NULL "
				+ " CONNECT BY PRIOR t.ID = t.P_ID ";
		return jdbcTemplate.queryForList(sql);
	}*/


	
	public int deleteByIds(String[] ids) {
	   final List<Object> updateParams=new ArrayList<>();
	   for(int i=0;i<ids.length;i++) {
		   String id=ids[i];
		   updateParams.add(id);
		}
		String sql= "update T_MD_TOPIC set DEL_FLAG=1, modified_time=? where id =?";	
		int[] execute = jdbcTemplate.batchUpdate(sql,new BatchPreparedStatementSetter() {
				
			@Override
			public void setValues(PreparedStatement ps, int i) throws SQLException {
				ps.setTimestamp(1, new Timestamp(System.currentTimeMillis()));
				ps.setString(2, updateParams.get(i).toString());
			}			
			@Override
			public int getBatchSize() {
				return updateParams.size();
			}
		});
		int reselt = execute.length;
		System.out.println("批量更新结果: " + reselt);
		return reselt;
	}

	/**
	 * 发布/ 撤回
	 */
	public int releaseOrRecall(String id, int status) {
		String sql= "update T_MD_TOPIC set status=? where id =?";	
		return jdbcTemplate.update(sql, status, id);
	}
	
	/**
	 * 根据topicQueueNo查询目标应用个数
	 * @param topicQueueNo
	 * @return
	 */
	public Integer queryTAppNum(String topicQueueNo) {
		String sql= "select count(1) from T_MD_TOPIC_SEND where DEL_FLAG = 0 AND TOPIC_QUEUE_NO =?";	
		return jdbcTemplate.queryForObject(sql, Integer.class, topicQueueNo);
	}
	
	/**
	 * 根据topicQueueNo查询源应用名称
	 * @param topicQueueNo
	 * @return
	 */
	public List<String> queryOriAppName(String topicQueueNo) {
		String sql = "select APP_NAME from T_MD_APP where DEL_FLAG = 0 and "
				+ " ID = (select ACCESS_APPID from T_MD_TOPIC_ACCESS where TOPIC_QUEUE_NO=?)";
		return jdbcTemplate.queryForList(sql.toString(), String.class, topicQueueNo);
	}

	/**
	 * 根据队列id查询队列的topicQueueNo
	 */
	public List<String> queryTopicQueueNoById(String[] ids) {
		StringBuffer sql = new StringBuffer("select TOPIC_QUEUE_NO from T_MD_TOPIC where ID in (");
		for(int i=0;i<ids.length;i++) {
			sql.append(" ? ");
			if(i!=ids.length-1) {
				sql.append(" , ");
			}
		}
		sql.append(" ) ");
		return jdbcTemplate.queryForList(sql.toString(), String.class, ids);
	}
	/**
	 * 根据topicQueueNo查询源应用用户名
	 */
	public List<String> queryOriAppUser(String topicQueueNo) {
		String sql = "select QUEUE_KEY from T_MD_APP where "
				+ " ID = (select ACCESS_APPID from T_MD_TOPIC_ACCESS where DEL_FLAG = 0 and TOPIC_QUEUE_NO=?)";
		return jdbcTemplate.queryForList(sql.toString(), String.class, topicQueueNo);
	}
	/**
	 * 查询源应用(根据消息队列编码查询)
	 */
	public List<MdApp> queryOriAppBySign(String topicQueueNo) {
		String sql = "select QUEUE_KEY,STATUS from T_MD_APP where DEL_FLAG = 0 and "
				+ " ID = (select ACCESS_APPID from T_MD_TOPIC_ACCESS where TOPIC_QUEUE_NO=?)";
		RowMapper<MdApp> rowMapper = new BeanPropertyRowMapper<>(MdApp.class);
		return jdbcTemplate.query(sql.toString(), rowMapper, topicQueueNo);
	}
	
	
	/**
	 * 根据topicQueueNo查询目标应用
	 */
	public List<MdApp> querytApp(String topicQueueNo) {
		String sql = "select QUEUE_KEY,STATUS from T_MD_APP where DEL_FLAG = 0 and "
				+ " ID in (select TARGET_APPID from T_MD_TOPIC_SEND where TOPIC_QUEUE_NO=? and DEL_FLAG = 0)";
		RowMapper<MdApp> rowMapper = new BeanPropertyRowMapper<>(MdApp.class);
		return jdbcTemplate.query(sql.toString(), rowMapper, topicQueueNo);
	}
	
	/**
	 * 查询递增的最后一条主题队列编码(删除的直接跳过)
	 */
	public List<String> queryAllQueueSigns() {
		String sql = "select TOPIC_QUEUE_NO from T_MD_TOPIC order by TOPIC_QUEUE_NO desc";
		return jdbcTemplate.queryForList(sql.toString(), String.class);
	}
	/**
	 * 保存消息队列
	 * @param id
	 * @param topicQueueName
	 * @param topicQueueNo
	 * @param accessType
	 * @param vhost
	 * @param queueName
	 * @param delFlag
	 * @param status
	 */
	public Integer saveQueue(String id, String topicQueueName, String topicQueueNo, String accessType, 
			 int delFlag, String status,int tAppNum) {
		String sql ="insert into T_MD_TOPIC (ID,CREATED_TIME,MODIFIED_TIME,STATUS, "
				+ " DEL_FLAG,TOPIC_QUEUE_NO,TOPIC_QUEUE_NAME,ACCESS_TYPE) "
				+ "values (?, ?, ?, ?, ?, ?, ?, ?)";
		return jdbcTemplate.update(sql, id,new Date(),new Date(),status,delFlag,topicQueueNo,topicQueueName,
				accessType);
		
	}
	//新增服务接入
	public Integer saveService(MdTopic mdTopic) {
		String sql="insert into T_MD_TOPIC (ID,ACCESS_TYPE,TOPIC_QUEUE_NO,"+
	"DEL_FLAG,VHOST_NAME,CREATED_TIME,MODIFIED_TIME,STATUS,EXCHANGE_NAME,TOPIC_QUEUE_NAME,TEMPLATE_ID)"
	+"values(?,?,?,?,?,?,?,?,?,?,?)";
		return jdbcTemplate.update(sql,UUIDUtil.getUUId(),"1401",mdTopic.getTopicQueueNo(),
				0,mdTopic.getVhostName(),new Date(),new Date(),
				"1301",mdTopic.getExchangeName(),mdTopic.getTopicQueueName(),mdTopic.getTemplateId());
	}
	
	/**
	 * 更新源应用
	 */
	public int updateOriApp(String tId, String originId) {
		String sql = "UPDATE T_MD_TOPIC_ACCESS SET ACCESS_APPID = ?,MODIFIED_TIME=? " + 
					" where TOPIC_QUEUE_NO = (SELECT TOPIC_QUEUE_NO FROM T_MD_TOPIC where ID = ?)";
		return jdbcTemplate.update(sql, originId, new Date(), tId);
	}
	
	/**
	 * 获取队列原有目标应用集合
	 * @param queueSign
	 * @return
	 */
	public List<String> getOriTApps(String queueSign) {
		String sql = "select ID from T_MD_TOPIC where TOPIC_QUEUE_NO = ?";
		return jdbcTemplate.queryForList(sql.toString(), String.class, queueSign);
	}
	
	/**
	 * 遍历逻辑删除目标应用
	 * @param tApps
	 * @return
	 */
	public int deleteTApps(String queueSign) {
		String sql = "update T_MD_TOPIC_SEND set DEL_FLAG=1, MODIFIED_TIME=? where TOPIC_QUEUE_NO =? and DEL_FLAG =0";
		return jdbcTemplate.update(sql,new Date(),queueSign);
	}
	
	/**
	 * 获取队列名称和编码
	 */
	public MdTopic queryTQAndOriApp(String tId) {
		/*String sql1 = "SELECT topic.TOPIC_QUEUE_NO as QUEUENO,topic.TOPIC_QUEUE_NAME as QUEUENAME," + 
				"(SELECT ID from T_MD_APP where ID = " + 
				"( SELECT ACCESS_APPID FROM T_MD_TOPIC_ACCESS where TOPIC_QUEUE_NO = ? and DEL_FLAG = 0)) as APPID," + 
				"(SELECT APP_NAME from T_MD_APP where ID = " + 
				"( SELECT ACCESS_APPID FROM T_MD_TOPIC_ACCESS where TOPIC_QUEUE_NO = ? and DEL_FLAG = 0)) as APPNAME " + 
				"from T_MD_TOPIC topic " + 
				"left join T_MD_TOPIC_ACCESS acc on topic.TOPIC_QUEUE_NO = acc.TOPIC_QUEUE_NO " + 
				"where topic.ID = ? and topic.DEL_FLAG = 0";*/
		String sql = " SELECT TOPIC_QUEUE_NO,TOPIC_QUEUE_NAME from T_MD_TOPIC where ID = ?";
		RowMapper<MdTopic> rowMapper = new BeanPropertyRowMapper<>(MdTopic.class);
		return jdbcTemplate.queryForObject(sql, rowMapper, tId);
	}
	
	/**
	 * 获取源应用信息
	 * @return
	 */
	public List<Map<String,Object>>getSourceAppName(){
		String sql="select t.app_name as appName,t.ID as id ,t.app_code as appCode\r\n" + 
				"from T_MD_App t\r\n" + 
				"where t.id in(select tmd.ACCESS_APPID\r\n" + 
				"from T_MD_TOPIC_ACCESS tmd\r\n" + 
				"where tmd.DEL_FLAG=0)";
		 List<Map<String,Object>> list=jdbcTemplate.queryForList(sql);
		return list;
	}
	
	/**
	 * 获取目标应用信息
	 * @return
	 */
	public List<Map<String,Object>>getTargetAppName(){
		String sql="select t.app_name as appName,t.ID as id ,t.app_code as appCode\r\n" + 
				"from T_MD_App t\r\n" + 
				"where t.id in(select tmd.target_APPID\r\n" + 
				"from T_MD_TOPIC_send tmd\r\n" + 
				"where tmd.DEL_FLAG=0)";
		 List<Map<String,Object>> list=jdbcTemplate.queryForList(sql);
		return list;
	}
}
